﻿using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using Cache;

namespace DataAccess
{
    public struct Result
    {
        public SqlParameterCollection Parameters;
        public DataSet DataSet;
    }

    public class DataSource
    {
        static string connectionString = ConfigurationManager.ConnectionStrings["popUpJoeDb"].ToString();
        static SqlConnection connection;

        public Result ExecuteDataSet(string storedProcedure, List<SqlParameter> parameters)
        {
            Result result = new Result();

            var cache = new Cacher(new HttpCache());
            string cacheKey = cache.GenCacheKey(storedProcedure.Replace("svc.", string.Empty), parameters);

            IEnumerable<List<string>> deps = null;

            if (cache.Enabled)
            {
                if (cacheKey != "uspGetDependenciesList")
                {
                    deps = from d in CacheDb.GetDependencies().AsEnumerable()
                           where d.Parent.ToLower().Equals(storedProcedure.Replace("svc.", string.Empty).ToLower())
                           select d.Dependencies;
                }

                if ((deps != null && deps.Count() > 0) || cacheKey == "uspGetDependenciesList")
                {
                    var cacheObj = cache.Fetch(cacheKey);

                    if (cacheObj != null)
                    {
                        result = (Result)cacheObj;
                    }
                }
            }

            if (result.DataSet == null || result.DataSet.Tables.Count == 0)
            {
                DataSet dataSet = new DataSet();
                SqlCommand command = GetCommand(storedProcedure, parameters);
                GetAdapter(command).Fill(dataSet);

                result = new Result() { DataSet = dataSet, Parameters = command.Parameters };

                if ((cache.Enabled && deps != null && deps.Count() > 0) || cacheKey == "uspGetDependenciesList")
                {
                    cache.Insert(cacheKey, result);
                }
            }         

            return result;
        }        

        public Result Execute(string storedProcedure, List<SqlParameter> parameters)
        {  
            var command = GetCommand(storedProcedure, parameters);

            if (Connection.State != ConnectionState.Open)
            {
                Connection.Open();
            }

            command.ExecuteNonQuery();
            Connection.Close();

            var cache = new Cacher(new HttpCache());

            if (cache.Enabled)
            {
                var deps = from d in CacheDb.GetDependencies().AsEnumerable()
                        where d.Parent.ToLower().Equals(storedProcedure.Replace("svc.", string.Empty).ToLower())
                        select d.Dependencies;

                if (deps != null && deps.Count() > 0)
                {
                    var keys = cache.GetAllKeys();

                    foreach (var dep in deps)
                    {
                        foreach (string sp in dep)
                        {
                            var q = from k in keys
                                    where k.ToLower().StartsWith(sp.ToLower())
                                    select k;

                            if (q != null && q.Count() > 0)
                            { 
                                foreach(var k in q)
                                {
                                    cache.Delete(k);
                                }
                            }
                        }
                    }
                }
            }

            Result result = new Result() { Parameters = command.Parameters };

            return result;
        }

        private SqlDataAdapter GetAdapter(SqlCommand command)
        {
            return new SqlDataAdapter(command);
        }

        private SqlCommand GetCommand(string storedProcedure, List<SqlParameter> parameters)
        {
            SqlCommand command = new SqlCommand(storedProcedure, Connection);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            if (parameters != null)
            {
                foreach (SqlParameter parameter in parameters)
                {
                    command.Parameters.Add(parameter);
                }
            }

            return command;
        }

        private SqlConnection Connection
        {
            get
            {
                if (connection == null)
                {
                    connection = new SqlConnection(connectionString);
                }

                return connection;
            }
        }
    }
}
